{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 航空公司客户价值分析"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 实验目的：<br>\n",
    "借助航空公司客户数据，对客户进行聚类。<br>\n",
    "对不同的客户类别进行特征分析，比较不同类别客户的客户价值。<br>\n",
    "对不同价值的客户类别提供个性化服务，制定相应的营销策略。<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 读取数据，指定编码为gb18030"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MEMBER_NO</th>\n",
       "      <th>FFP_DATE</th>\n",
       "      <th>FIRST_FLIGHT_DATE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>FFP_TIER</th>\n",
       "      <th>WORK_CITY</th>\n",
       "      <th>WORK_PROVINCE</th>\n",
       "      <th>WORK_COUNTRY</th>\n",
       "      <th>AGE</th>\n",
       "      <th>LOAD_TIME</th>\n",
       "      <th>...</th>\n",
       "      <th>ADD_Point_SUM</th>\n",
       "      <th>Eli_Add_Point_Sum</th>\n",
       "      <th>L1Y_ELi_Add_Points</th>\n",
       "      <th>Points_Sum</th>\n",
       "      <th>L1Y_Points_Sum</th>\n",
       "      <th>Ration_L1Y_Flight_Count</th>\n",
       "      <th>Ration_P1Y_Flight_Count</th>\n",
       "      <th>Ration_P1Y_BPS</th>\n",
       "      <th>Ration_L1Y_BPS</th>\n",
       "      <th>Point_NotFlight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54993</td>\n",
       "      <td>2006/11/2</td>\n",
       "      <td>2008/12/24</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>.</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>39992</td>\n",
       "      <td>114452</td>\n",
       "      <td>111100</td>\n",
       "      <td>619760</td>\n",
       "      <td>370211</td>\n",
       "      <td>0.509524</td>\n",
       "      <td>0.490476</td>\n",
       "      <td>0.487221</td>\n",
       "      <td>0.512777</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28065</td>\n",
       "      <td>2007/2/19</td>\n",
       "      <td>2007/8/3</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>42.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>12000</td>\n",
       "      <td>53288</td>\n",
       "      <td>53288</td>\n",
       "      <td>415768</td>\n",
       "      <td>238410</td>\n",
       "      <td>0.514286</td>\n",
       "      <td>0.485714</td>\n",
       "      <td>0.489289</td>\n",
       "      <td>0.510708</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55106</td>\n",
       "      <td>2007/2/1</td>\n",
       "      <td>2007/8/30</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>.</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>15491</td>\n",
       "      <td>55202</td>\n",
       "      <td>51711</td>\n",
       "      <td>406361</td>\n",
       "      <td>233798</td>\n",
       "      <td>0.518519</td>\n",
       "      <td>0.481481</td>\n",
       "      <td>0.481467</td>\n",
       "      <td>0.518530</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21189</td>\n",
       "      <td>2008/8/22</td>\n",
       "      <td>2008/8/23</td>\n",
       "      <td>男</td>\n",
       "      <td>5</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>US</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>34890</td>\n",
       "      <td>34890</td>\n",
       "      <td>372204</td>\n",
       "      <td>186100</td>\n",
       "      <td>0.434783</td>\n",
       "      <td>0.565217</td>\n",
       "      <td>0.551722</td>\n",
       "      <td>0.448275</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39546</td>\n",
       "      <td>2009/4/10</td>\n",
       "      <td>2009/4/15</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>贵阳</td>\n",
       "      <td>贵州</td>\n",
       "      <td>CN</td>\n",
       "      <td>48.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>22704</td>\n",
       "      <td>64969</td>\n",
       "      <td>64969</td>\n",
       "      <td>338813</td>\n",
       "      <td>210365</td>\n",
       "      <td>0.532895</td>\n",
       "      <td>0.467105</td>\n",
       "      <td>0.469054</td>\n",
       "      <td>0.530943</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   MEMBER_NO   FFP_DATE FIRST_FLIGHT_DATE GENDER  FFP_TIER    WORK_CITY  \\\n",
       "0      54993  2006/11/2        2008/12/24      男         6            .   \n",
       "1      28065  2007/2/19          2007/8/3      男         6          NaN   \n",
       "2      55106   2007/2/1         2007/8/30      男         6            .   \n",
       "3      21189  2008/8/22         2008/8/23      男         5  Los Angeles   \n",
       "4      39546  2009/4/10         2009/4/15      男         6           贵阳   \n",
       "\n",
       "  WORK_PROVINCE WORK_COUNTRY   AGE  LOAD_TIME  ...  ADD_Point_SUM  \\\n",
       "0            北京           CN  31.0  2014/3/31  ...          39992   \n",
       "1            北京           CN  42.0  2014/3/31  ...          12000   \n",
       "2            北京           CN  40.0  2014/3/31  ...          15491   \n",
       "3            CA           US  64.0  2014/3/31  ...              0   \n",
       "4            贵州           CN  48.0  2014/3/31  ...          22704   \n",
       "\n",
       "   Eli_Add_Point_Sum  L1Y_ELi_Add_Points  Points_Sum  L1Y_Points_Sum  \\\n",
       "0             114452              111100      619760          370211   \n",
       "1              53288               53288      415768          238410   \n",
       "2              55202               51711      406361          233798   \n",
       "3              34890               34890      372204          186100   \n",
       "4              64969               64969      338813          210365   \n",
       "\n",
       "   Ration_L1Y_Flight_Count  Ration_P1Y_Flight_Count  Ration_P1Y_BPS  \\\n",
       "0                 0.509524                 0.490476        0.487221   \n",
       "1                 0.514286                 0.485714        0.489289   \n",
       "2                 0.518519                 0.481481        0.481467   \n",
       "3                 0.434783                 0.565217        0.551722   \n",
       "4                 0.532895                 0.467105        0.469054   \n",
       "\n",
       "  Ration_L1Y_BPS  Point_NotFlight  \n",
       "0       0.512777               50  \n",
       "1       0.510708               33  \n",
       "2       0.518530               26  \n",
       "3       0.448275               12  \n",
       "4       0.530943               39  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "airline_data=pd.read_csv('../data/air_data.csv',\n",
    "                        encoding='gb18030')\n",
    "airline_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='../data/data1.png'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='../data/data2.png'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据描述性分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 62988 entries, 0 to 62987\n",
      "Data columns (total 44 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   MEMBER_NO                62988 non-null  int64  \n",
      " 1   FFP_DATE                 62988 non-null  object \n",
      " 2   FIRST_FLIGHT_DATE        62988 non-null  object \n",
      " 3   GENDER                   62985 non-null  object \n",
      " 4   FFP_TIER                 62988 non-null  int64  \n",
      " 5   WORK_CITY                60719 non-null  object \n",
      " 6   WORK_PROVINCE            59740 non-null  object \n",
      " 7   WORK_COUNTRY             62962 non-null  object \n",
      " 8   AGE                      62568 non-null  float64\n",
      " 9   LOAD_TIME                62988 non-null  object \n",
      " 10  FLIGHT_COUNT             62988 non-null  int64  \n",
      " 11  BP_SUM                   62988 non-null  int64  \n",
      " 12  EP_SUM_YR_1              62988 non-null  int64  \n",
      " 13  EP_SUM_YR_2              62988 non-null  int64  \n",
      " 14  SUM_YR_1                 62437 non-null  float64\n",
      " 15  SUM_YR_2                 62850 non-null  float64\n",
      " 16  SEG_KM_SUM               62988 non-null  int64  \n",
      " 17  WEIGHTED_SEG_KM          62988 non-null  float64\n",
      " 18  LAST_FLIGHT_DATE         62988 non-null  object \n",
      " 19  AVG_FLIGHT_COUNT         62988 non-null  float64\n",
      " 20  AVG_BP_SUM               62988 non-null  float64\n",
      " 21  BEGIN_TO_FIRST           62988 non-null  int64  \n",
      " 22  LAST_TO_END              62988 non-null  int64  \n",
      " 23  AVG_INTERVAL             62988 non-null  float64\n",
      " 24  MAX_INTERVAL             62988 non-null  int64  \n",
      " 25  ADD_POINTS_SUM_YR_1      62988 non-null  int64  \n",
      " 26  ADD_POINTS_SUM_YR_2      62988 non-null  int64  \n",
      " 27  EXCHANGE_COUNT           62988 non-null  int64  \n",
      " 28  avg_discount             62988 non-null  float64\n",
      " 29  P1Y_Flight_Count         62988 non-null  int64  \n",
      " 30  L1Y_Flight_Count         62988 non-null  int64  \n",
      " 31  P1Y_BP_SUM               62988 non-null  int64  \n",
      " 32  L1Y_BP_SUM               62988 non-null  int64  \n",
      " 33  EP_SUM                   62988 non-null  int64  \n",
      " 34  ADD_Point_SUM            62988 non-null  int64  \n",
      " 35  Eli_Add_Point_Sum        62988 non-null  int64  \n",
      " 36  L1Y_ELi_Add_Points       62988 non-null  int64  \n",
      " 37  Points_Sum               62988 non-null  int64  \n",
      " 38  L1Y_Points_Sum           62988 non-null  int64  \n",
      " 39  Ration_L1Y_Flight_Count  62988 non-null  float64\n",
      " 40  Ration_P1Y_Flight_Count  62988 non-null  float64\n",
      " 41  Ration_P1Y_BPS           62988 non-null  float64\n",
      " 42  Ration_L1Y_BPS           62988 non-null  float64\n",
      " 43  Point_NotFlight          62988 non-null  int64  \n",
      "dtypes: float64(12), int64(24), object(8)\n",
      "memory usage: 21.1+ MB\n"
     ]
    }
   ],
   "source": [
    "airline_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据预处理<br>\n",
    "#### 1. 去除票价为空的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62299, 44)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp1=airline_data['SUM_YR_1'].notnull()\n",
    "exp2=airline_data['SUM_YR_2'].notnull()\n",
    "exp=exp1&exp2\n",
    "airline_notnull=airline_data.loc[exp,:]\n",
    "airline_notnull.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.只保留票价不为0，平均折扣率不为0，总飞行公里数大于0的记录。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(62044, 44)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index1=airline_notnull['SUM_YR_1']!=0\n",
    "index2=airline_notnull['SUM_YR_2']!=0\n",
    "index3=(airline_notnull['avg_discount']!=0)&(airline_notnull['SEG_KM_SUM']>0)\n",
    "airline=airline_notnull[(index1|index2)&index3]\n",
    "airline.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 构建特征"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src='../data/1.jpg'>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "L: LOAD_TIME  观测窗口的结束时间----FFP_DATE\t入会时间<br>\n",
    "R: LAST_TO_END  最后一次乘机时间至观测窗口结束时长<br>\n",
    "F: FLIGHT_COUNT 观测窗口内的飞行次数 <br>\n",
    "M: SEG_KM_SUM 观测窗口的总飞行公里数 <br>\n",
    "C: avg_discount 平均折扣率<br>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LOAD_TIME</th>\n",
       "      <th>FFP_DATE</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>2006/11/2</td>\n",
       "      <td>1</td>\n",
       "      <td>210</td>\n",
       "      <td>580717</td>\n",
       "      <td>0.961639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>2007/2/19</td>\n",
       "      <td>7</td>\n",
       "      <td>140</td>\n",
       "      <td>293678</td>\n",
       "      <td>1.252314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>2007/2/1</td>\n",
       "      <td>11</td>\n",
       "      <td>135</td>\n",
       "      <td>283712</td>\n",
       "      <td>1.254676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>2008/8/22</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>281336</td>\n",
       "      <td>1.090870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>2009/4/10</td>\n",
       "      <td>5</td>\n",
       "      <td>152</td>\n",
       "      <td>309928</td>\n",
       "      <td>0.970658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   LOAD_TIME   FFP_DATE  LAST_TO_END  FLIGHT_COUNT  SEG_KM_SUM  avg_discount\n",
       "0  2014/3/31  2006/11/2            1           210      580717      0.961639\n",
       "1  2014/3/31  2007/2/19            7           140      293678      1.252314\n",
       "2  2014/3/31   2007/2/1           11           135      283712      1.254676\n",
       "3  2014/3/31  2008/8/22           97            23      281336      1.090870\n",
       "4  2014/3/31  2009/4/10            5           152      309928      0.970658"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 构建特征 按照顺序将需要的列选择出来 \n",
    "# airline_selection\n",
    "airline_selection=airline[['LOAD_TIME',\n",
    "                          'FFP_DATE',\n",
    "                          'LAST_TO_END',\n",
    "                          'FLIGHT_COUNT',\n",
    "                          'SEG_KM_SUM',\n",
    "                          'avg_discount']]\n",
    "airline_selection.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 62044 entries, 0 to 62978\n",
      "Data columns (total 6 columns):\n",
      " #   Column        Non-Null Count  Dtype  \n",
      "---  ------        --------------  -----  \n",
      " 0   LOAD_TIME     62044 non-null  object \n",
      " 1   FFP_DATE      62044 non-null  object \n",
      " 2   LAST_TO_END   62044 non-null  int64  \n",
      " 3   FLIGHT_COUNT  62044 non-null  int64  \n",
      " 4   SEG_KM_SUM    62044 non-null  int64  \n",
      " 5   avg_discount  62044 non-null  float64\n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 3.3+ MB\n"
     ]
    }
   ],
   "source": [
    "airline_selection.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 修改数据类型 日期 pd.to_datetime\n",
    "airline_selection['LOAD_TIME']=pd.to_datetime(airline_selection['LOAD_TIME'])\n",
    "airline_selection['FFP_DATE']=pd.to_datetime(airline_selection['FFP_DATE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 62044 entries, 0 to 62978\n",
      "Data columns (total 6 columns):\n",
      " #   Column        Non-Null Count  Dtype         \n",
      "---  ------        --------------  -----         \n",
      " 0   LOAD_TIME     62044 non-null  datetime64[ns]\n",
      " 1   FFP_DATE      62044 non-null  datetime64[ns]\n",
      " 2   LAST_TO_END   62044 non-null  int64         \n",
      " 3   FLIGHT_COUNT  62044 non-null  int64         \n",
      " 4   SEG_KM_SUM    62044 non-null  int64         \n",
      " 5   avg_discount  62044 non-null  float64       \n",
      "dtypes: datetime64[ns](2), float64(1), int64(3)\n",
      "memory usage: 3.3 MB\n"
     ]
    }
   ],
   "source": [
    "airline_selection.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         90.20\n",
       "1         86.57\n",
       "2         87.17\n",
       "3         68.23\n",
       "4         60.53\n",
       "          ...  \n",
       "62974    108.30\n",
       "62975     65.37\n",
       "62976     45.40\n",
       "62977     15.53\n",
       "62978     36.07\n",
       "Length: 62044, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建L特征 观测窗口的结束时间-入会时间\n",
    "L=airline_selection['LOAD_TIME']-airline_selection['FFP_DATE']\n",
    "L\n",
    "# series数据进行切割\n",
    "L=L.astype('str').str.split().str[0]\n",
    "L=L.astype('int')/30\n",
    "L=np.round(L,2)\n",
    "L"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90.20</td>\n",
       "      <td>1</td>\n",
       "      <td>210</td>\n",
       "      <td>580717</td>\n",
       "      <td>0.961639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86.57</td>\n",
       "      <td>7</td>\n",
       "      <td>140</td>\n",
       "      <td>293678</td>\n",
       "      <td>1.252314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>87.17</td>\n",
       "      <td>11</td>\n",
       "      <td>135</td>\n",
       "      <td>283712</td>\n",
       "      <td>1.254676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68.23</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>281336</td>\n",
       "      <td>1.090870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60.53</td>\n",
       "      <td>5</td>\n",
       "      <td>152</td>\n",
       "      <td>309928</td>\n",
       "      <td>0.970658</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       0  LAST_TO_END  FLIGHT_COUNT  SEG_KM_SUM  avg_discount\n",
       "0  90.20            1           210      580717      0.961639\n",
       "1  86.57            7           140      293678      1.252314\n",
       "2  87.17           11           135      283712      1.254676\n",
       "3  68.23           97            23      281336      1.090870\n",
       "4  60.53            5           152      309928      0.970658"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# l特征和原有特征合并\n",
    "airline_features=pd.concat(objs=[L,airline_selection.iloc[:,2:]]\n",
    "                           ,axis=1)\n",
    "airline_features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90.20</td>\n",
       "      <td>1</td>\n",
       "      <td>210</td>\n",
       "      <td>580717</td>\n",
       "      <td>0.961639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86.57</td>\n",
       "      <td>7</td>\n",
       "      <td>140</td>\n",
       "      <td>293678</td>\n",
       "      <td>1.252314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>87.17</td>\n",
       "      <td>11</td>\n",
       "      <td>135</td>\n",
       "      <td>283712</td>\n",
       "      <td>1.254676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68.23</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>281336</td>\n",
       "      <td>1.090870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60.53</td>\n",
       "      <td>5</td>\n",
       "      <td>152</td>\n",
       "      <td>309928</td>\n",
       "      <td>0.970658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62974</th>\n",
       "      <td>108.30</td>\n",
       "      <td>89</td>\n",
       "      <td>2</td>\n",
       "      <td>368</td>\n",
       "      <td>0.710000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62975</th>\n",
       "      <td>65.37</td>\n",
       "      <td>121</td>\n",
       "      <td>2</td>\n",
       "      <td>368</td>\n",
       "      <td>0.670000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62976</th>\n",
       "      <td>45.40</td>\n",
       "      <td>39</td>\n",
       "      <td>2</td>\n",
       "      <td>1062</td>\n",
       "      <td>0.225000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62977</th>\n",
       "      <td>15.53</td>\n",
       "      <td>464</td>\n",
       "      <td>2</td>\n",
       "      <td>904</td>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62978</th>\n",
       "      <td>36.07</td>\n",
       "      <td>282</td>\n",
       "      <td>2</td>\n",
       "      <td>760</td>\n",
       "      <td>0.280000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62044 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            L  LAST_TO_END  FLIGHT_COUNT  SEG_KM_SUM  avg_discount\n",
       "0       90.20            1           210      580717      0.961639\n",
       "1       86.57            7           140      293678      1.252314\n",
       "2       87.17           11           135      283712      1.254676\n",
       "3       68.23           97            23      281336      1.090870\n",
       "4       60.53            5           152      309928      0.970658\n",
       "...       ...          ...           ...         ...           ...\n",
       "62974  108.30           89             2         368      0.710000\n",
       "62975   65.37          121             2         368      0.670000\n",
       "62976   45.40           39             2        1062      0.225000\n",
       "62977   15.53          464             2         904      0.250000\n",
       "62978   36.07          282             2         760      0.280000\n",
       "\n",
       "[62044 rows x 5 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airline_features=airline_features.rename(columns={0:'L'})\n",
    "airline_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>62044.000000</td>\n",
       "      <td>62044.000000</td>\n",
       "      <td>62044.000000</td>\n",
       "      <td>62044.000000</td>\n",
       "      <td>62044.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>49.623035</td>\n",
       "      <td>172.532703</td>\n",
       "      <td>11.971359</td>\n",
       "      <td>17321.694749</td>\n",
       "      <td>0.722180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>28.262726</td>\n",
       "      <td>181.526164</td>\n",
       "      <td>14.110619</td>\n",
       "      <td>21052.728111</td>\n",
       "      <td>0.184833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>12.170000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>368.000000</td>\n",
       "      <td>0.136017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>24.500000</td>\n",
       "      <td>29.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4874.000000</td>\n",
       "      <td>0.613085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>42.600000</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>10200.000000</td>\n",
       "      <td>0.712162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>72.730000</td>\n",
       "      <td>260.000000</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>21522.500000</td>\n",
       "      <td>0.809293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>114.570000</td>\n",
       "      <td>731.000000</td>\n",
       "      <td>213.000000</td>\n",
       "      <td>580717.000000</td>\n",
       "      <td>1.500000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  L   LAST_TO_END  FLIGHT_COUNT     SEG_KM_SUM  avg_discount\n",
       "count  62044.000000  62044.000000  62044.000000   62044.000000  62044.000000\n",
       "mean      49.623035    172.532703     11.971359   17321.694749      0.722180\n",
       "std       28.262726    181.526164     14.110619   21052.728111      0.184833\n",
       "min       12.170000      1.000000      2.000000     368.000000      0.136017\n",
       "25%       24.500000     29.000000      3.000000    4874.000000      0.613085\n",
       "50%       42.600000    105.000000      7.000000   10200.000000      0.712162\n",
       "75%       72.730000    260.000000     15.000000   21522.500000      0.809293\n",
       "max      114.570000    731.000000    213.000000  580717.000000      1.500000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airline_features.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据标准化处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.435706</td>\n",
       "      <td>-0.944948</td>\n",
       "      <td>14.034016</td>\n",
       "      <td>26.761154</td>\n",
       "      <td>1.295540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.307268</td>\n",
       "      <td>-0.911894</td>\n",
       "      <td>9.073213</td>\n",
       "      <td>13.126864</td>\n",
       "      <td>2.868176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.328498</td>\n",
       "      <td>-0.889859</td>\n",
       "      <td>8.718869</td>\n",
       "      <td>12.653481</td>\n",
       "      <td>2.880950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.658357</td>\n",
       "      <td>-0.416098</td>\n",
       "      <td>0.781585</td>\n",
       "      <td>12.540622</td>\n",
       "      <td>1.994714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.385913</td>\n",
       "      <td>-0.922912</td>\n",
       "      <td>9.923636</td>\n",
       "      <td>13.898736</td>\n",
       "      <td>1.344335</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          L  LAST_TO_END  FLIGHT_COUNT  SEG_KM_SUM  avg_discount\n",
       "0  1.435706    -0.944948     14.034016   26.761154      1.295540\n",
       "1  1.307268    -0.911894      9.073213   13.126864      2.868176\n",
       "2  1.328498    -0.889859      8.718869   12.653481      2.880950\n",
       "3  0.658357    -0.416098      0.781585   12.540622      1.994714\n",
       "4  0.385913    -0.922912      9.923636   13.898736      1.344335"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airline_features_scaled=(airline_features-airline_features.mean())/airline_features.std()\n",
    "airline_features_scaled.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用k均值构建模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.cluster import KMeans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "KMeans(algorithm='auto', copy_x=True, init='k-means++', max_iter=300,\n",
       "       n_clusters=5, n_init=10, n_jobs=None, precompute_distances='auto',\n",
       "       random_state=123, tol=0.0001, verbose=0)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 确定聚类中心数\n",
    "k=5\n",
    "kmeans_model=KMeans(n_clusters=k,random_state=123).fit(airline_features_scaled)\n",
    "kmeans_model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1, 1, 1, ..., 4, 2, 2], dtype=int32)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 得到聚类标签 label\n",
    "# 每一个样本都有一个标签、\n",
    "# 相同标签的是属于同一个类别\n",
    "kmeans_model.labels_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 4.65625727e-02, -1.99113055e-03, -2.30142183e-01,\n",
       "        -2.34464735e-01,  2.17865520e+00],\n",
       "       [ 4.83657973e-01, -7.99400212e-01,  2.48317490e+00,\n",
       "         2.42445945e+00,  3.09237966e-01],\n",
       "       [-3.13368048e-01,  1.68669161e+00, -5.73935737e-01,\n",
       "        -5.36782673e-01, -1.74608430e-01],\n",
       "       [ 1.16084862e+00, -3.77377217e-01, -8.66405041e-02,\n",
       "        -9.45551300e-02, -1.56599649e-01],\n",
       "       [-7.00313344e-01, -4.15035589e-01, -1.60898126e-01,\n",
       "        -1.60646112e-01, -2.56723170e-01]])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 得到聚类中心\n",
    "# 5行 五个类别 分别是01234的聚类中心\n",
    "# 5列 5个特征 分别是LRFMC\n",
    "# 聚类中心的意义：代表当前类别平均情况\n",
    "kmeans_model.cluster_centers_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分析不同类别的特点 定义客户价值标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.046563</td>\n",
       "      <td>-0.001991</td>\n",
       "      <td>-0.230142</td>\n",
       "      <td>-0.234465</td>\n",
       "      <td>2.178655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.483658</td>\n",
       "      <td>-0.799400</td>\n",
       "      <td>2.483175</td>\n",
       "      <td>2.424459</td>\n",
       "      <td>0.309238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.313368</td>\n",
       "      <td>1.686692</td>\n",
       "      <td>-0.573936</td>\n",
       "      <td>-0.536783</td>\n",
       "      <td>-0.174608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.160849</td>\n",
       "      <td>-0.377377</td>\n",
       "      <td>-0.086641</td>\n",
       "      <td>-0.094555</td>\n",
       "      <td>-0.156600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.700313</td>\n",
       "      <td>-0.415036</td>\n",
       "      <td>-0.160898</td>\n",
       "      <td>-0.160646</td>\n",
       "      <td>-0.256723</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2         3         4\n",
       "0  0.046563 -0.001991 -0.230142 -0.234465  2.178655\n",
       "1  0.483658 -0.799400  2.483175  2.424459  0.309238\n",
       "2 -0.313368  1.686692 -0.573936 -0.536783 -0.174608\n",
       "3  1.160849 -0.377377 -0.086641 -0.094555 -0.156600\n",
       "4 -0.700313 -0.415036 -0.160898 -0.160646 -0.256723"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.DataFrame(data=kmeans_model.cluster_centers_)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('df.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4    24638\n",
       "3    15735\n",
       "2    12119\n",
       "1     5337\n",
       "0     4215\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计不同类别的数量\n",
    "s1=pd.Series(kmeans_model.labels_)\n",
    "s1.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MEMBER_NO</th>\n",
       "      <th>FFP_DATE</th>\n",
       "      <th>FIRST_FLIGHT_DATE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>FFP_TIER</th>\n",
       "      <th>WORK_CITY</th>\n",
       "      <th>WORK_PROVINCE</th>\n",
       "      <th>WORK_COUNTRY</th>\n",
       "      <th>AGE</th>\n",
       "      <th>LOAD_TIME</th>\n",
       "      <th>...</th>\n",
       "      <th>ADD_Point_SUM</th>\n",
       "      <th>Eli_Add_Point_Sum</th>\n",
       "      <th>L1Y_ELi_Add_Points</th>\n",
       "      <th>Points_Sum</th>\n",
       "      <th>L1Y_Points_Sum</th>\n",
       "      <th>Ration_L1Y_Flight_Count</th>\n",
       "      <th>Ration_P1Y_Flight_Count</th>\n",
       "      <th>Ration_P1Y_BPS</th>\n",
       "      <th>Ration_L1Y_BPS</th>\n",
       "      <th>Point_NotFlight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54993</td>\n",
       "      <td>2006/11/2</td>\n",
       "      <td>2008/12/24</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>.</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>39992</td>\n",
       "      <td>114452</td>\n",
       "      <td>111100</td>\n",
       "      <td>619760</td>\n",
       "      <td>370211</td>\n",
       "      <td>0.509524</td>\n",
       "      <td>0.490476</td>\n",
       "      <td>0.487221</td>\n",
       "      <td>0.512777</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28065</td>\n",
       "      <td>2007/2/19</td>\n",
       "      <td>2007/8/3</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>42.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>12000</td>\n",
       "      <td>53288</td>\n",
       "      <td>53288</td>\n",
       "      <td>415768</td>\n",
       "      <td>238410</td>\n",
       "      <td>0.514286</td>\n",
       "      <td>0.485714</td>\n",
       "      <td>0.489289</td>\n",
       "      <td>0.510708</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55106</td>\n",
       "      <td>2007/2/1</td>\n",
       "      <td>2007/8/30</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>.</td>\n",
       "      <td>北京</td>\n",
       "      <td>CN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>15491</td>\n",
       "      <td>55202</td>\n",
       "      <td>51711</td>\n",
       "      <td>406361</td>\n",
       "      <td>233798</td>\n",
       "      <td>0.518519</td>\n",
       "      <td>0.481481</td>\n",
       "      <td>0.481467</td>\n",
       "      <td>0.518530</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21189</td>\n",
       "      <td>2008/8/22</td>\n",
       "      <td>2008/8/23</td>\n",
       "      <td>男</td>\n",
       "      <td>5</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>CA</td>\n",
       "      <td>US</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>34890</td>\n",
       "      <td>34890</td>\n",
       "      <td>372204</td>\n",
       "      <td>186100</td>\n",
       "      <td>0.434783</td>\n",
       "      <td>0.565217</td>\n",
       "      <td>0.551722</td>\n",
       "      <td>0.448275</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39546</td>\n",
       "      <td>2009/4/10</td>\n",
       "      <td>2009/4/15</td>\n",
       "      <td>男</td>\n",
       "      <td>6</td>\n",
       "      <td>贵阳</td>\n",
       "      <td>贵州</td>\n",
       "      <td>CN</td>\n",
       "      <td>48.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>22704</td>\n",
       "      <td>64969</td>\n",
       "      <td>64969</td>\n",
       "      <td>338813</td>\n",
       "      <td>210365</td>\n",
       "      <td>0.532895</td>\n",
       "      <td>0.467105</td>\n",
       "      <td>0.469054</td>\n",
       "      <td>0.530943</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62974</th>\n",
       "      <td>11163</td>\n",
       "      <td>2005/5/8</td>\n",
       "      <td>2005/8/26</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>500</td>\n",
       "      <td>500</td>\n",
       "      <td>500</td>\n",
       "      <td>900</td>\n",
       "      <td>900</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.997506</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62975</th>\n",
       "      <td>30765</td>\n",
       "      <td>2008/11/16</td>\n",
       "      <td>2013/11/30</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>TAIPEI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TW</td>\n",
       "      <td>38.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>400</td>\n",
       "      <td>400</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.997506</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62976</th>\n",
       "      <td>10380</td>\n",
       "      <td>2010/7/8</td>\n",
       "      <td>2011/6/21</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>贵阳市</td>\n",
       "      <td>贵州省</td>\n",
       "      <td>CN</td>\n",
       "      <td>33.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>213</td>\n",
       "      <td>0</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.995327</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62977</th>\n",
       "      <td>16372</td>\n",
       "      <td>2012/12/20</td>\n",
       "      <td>2012/12/20</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>桃园</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TW</td>\n",
       "      <td>47.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62978</th>\n",
       "      <td>22761</td>\n",
       "      <td>2011/4/14</td>\n",
       "      <td>2011/4/14</td>\n",
       "      <td>男</td>\n",
       "      <td>4</td>\n",
       "      <td>汕头</td>\n",
       "      <td>广东省</td>\n",
       "      <td>CN</td>\n",
       "      <td>48.0</td>\n",
       "      <td>2014/3/31</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62044 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       MEMBER_NO    FFP_DATE FIRST_FLIGHT_DATE GENDER  FFP_TIER    WORK_CITY  \\\n",
       "0          54993   2006/11/2        2008/12/24      男         6            .   \n",
       "1          28065   2007/2/19          2007/8/3      男         6          NaN   \n",
       "2          55106    2007/2/1         2007/8/30      男         6            .   \n",
       "3          21189   2008/8/22         2008/8/23      男         5  Los Angeles   \n",
       "4          39546   2009/4/10         2009/4/15      男         6           贵阳   \n",
       "...          ...         ...               ...    ...       ...          ...   \n",
       "62974      11163    2005/5/8         2005/8/26      男         4          NaN   \n",
       "62975      30765  2008/11/16        2013/11/30      男         4       TAIPEI   \n",
       "62976      10380    2010/7/8         2011/6/21      男         4          贵阳市   \n",
       "62977      16372  2012/12/20        2012/12/20      男         4           桃园   \n",
       "62978      22761   2011/4/14         2011/4/14      男         4           汕头   \n",
       "\n",
       "      WORK_PROVINCE WORK_COUNTRY   AGE  LOAD_TIME  ...  ADD_Point_SUM  \\\n",
       "0                北京           CN  31.0  2014/3/31  ...          39992   \n",
       "1                北京           CN  42.0  2014/3/31  ...          12000   \n",
       "2                北京           CN  40.0  2014/3/31  ...          15491   \n",
       "3                CA           US  64.0  2014/3/31  ...              0   \n",
       "4                贵州           CN  48.0  2014/3/31  ...          22704   \n",
       "...             ...          ...   ...        ...  ...            ...   \n",
       "62974           NaN           CN  34.0  2014/3/31  ...            500   \n",
       "62975           NaN           TW  38.0  2014/3/31  ...              0   \n",
       "62976           贵州省           CN  33.0  2014/3/31  ...              0   \n",
       "62977           NaN           TW  47.0  2014/3/31  ...              0   \n",
       "62978           广东省           CN  48.0  2014/3/31  ...              0   \n",
       "\n",
       "       Eli_Add_Point_Sum  L1Y_ELi_Add_Points  Points_Sum  L1Y_Points_Sum  \\\n",
       "0                 114452              111100      619760          370211   \n",
       "1                  53288               53288      415768          238410   \n",
       "2                  55202               51711      406361          233798   \n",
       "3                  34890               34890      372204          186100   \n",
       "4                  64969               64969      338813          210365   \n",
       "...                  ...                 ...         ...             ...   \n",
       "62974                500                 500         900             900   \n",
       "62975                  0                   0         400             400   \n",
       "62976                  0                   0         213               0   \n",
       "62977                  0                   0           0               0   \n",
       "62978                  0                   0           0               0   \n",
       "\n",
       "       Ration_L1Y_Flight_Count  Ration_P1Y_Flight_Count  Ration_P1Y_BPS  \\\n",
       "0                     0.509524                 0.490476        0.487221   \n",
       "1                     0.514286                 0.485714        0.489289   \n",
       "2                     0.518519                 0.481481        0.481467   \n",
       "3                     0.434783                 0.565217        0.551722   \n",
       "4                     0.532895                 0.467105        0.469054   \n",
       "...                        ...                      ...             ...   \n",
       "62974                 1.000000                 0.000000        0.000000   \n",
       "62975                 1.000000                 0.000000        0.000000   \n",
       "62976                 0.500000                 0.500000        0.995327   \n",
       "62977                 0.000000                 1.000000        0.000000   \n",
       "62978                 1.000000                 0.000000        0.000000   \n",
       "\n",
       "      Ration_L1Y_BPS  Point_NotFlight  \n",
       "0           0.512777               50  \n",
       "1           0.510708               33  \n",
       "2           0.518530               26  \n",
       "3           0.448275               12  \n",
       "4           0.530943               39  \n",
       "...              ...              ...  \n",
       "62974       0.997506                1  \n",
       "62975       0.997506                0  \n",
       "62976       0.000000                1  \n",
       "62977       0.000000                0  \n",
       "62978       0.000000                0  \n",
       "\n",
       "[62044 rows x 44 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>L</th>\n",
       "      <th>LAST_TO_END</th>\n",
       "      <th>FLIGHT_COUNT</th>\n",
       "      <th>SEG_KM_SUM</th>\n",
       "      <th>avg_discount</th>\n",
       "      <th>label</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90.20</td>\n",
       "      <td>1</td>\n",
       "      <td>210</td>\n",
       "      <td>580717</td>\n",
       "      <td>0.961639</td>\n",
       "      <td>1</td>\n",
       "      <td>54993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>86.57</td>\n",
       "      <td>7</td>\n",
       "      <td>140</td>\n",
       "      <td>293678</td>\n",
       "      <td>1.252314</td>\n",
       "      <td>1</td>\n",
       "      <td>28065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>87.17</td>\n",
       "      <td>11</td>\n",
       "      <td>135</td>\n",
       "      <td>283712</td>\n",
       "      <td>1.254676</td>\n",
       "      <td>1</td>\n",
       "      <td>55106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>68.23</td>\n",
       "      <td>97</td>\n",
       "      <td>23</td>\n",
       "      <td>281336</td>\n",
       "      <td>1.090870</td>\n",
       "      <td>1</td>\n",
       "      <td>21189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60.53</td>\n",
       "      <td>5</td>\n",
       "      <td>152</td>\n",
       "      <td>309928</td>\n",
       "      <td>0.970658</td>\n",
       "      <td>1</td>\n",
       "      <td>39546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62974</th>\n",
       "      <td>108.30</td>\n",
       "      <td>89</td>\n",
       "      <td>2</td>\n",
       "      <td>368</td>\n",
       "      <td>0.710000</td>\n",
       "      <td>3</td>\n",
       "      <td>11163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62975</th>\n",
       "      <td>65.37</td>\n",
       "      <td>121</td>\n",
       "      <td>2</td>\n",
       "      <td>368</td>\n",
       "      <td>0.670000</td>\n",
       "      <td>3</td>\n",
       "      <td>30765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62976</th>\n",
       "      <td>45.40</td>\n",
       "      <td>39</td>\n",
       "      <td>2</td>\n",
       "      <td>1062</td>\n",
       "      <td>0.225000</td>\n",
       "      <td>4</td>\n",
       "      <td>10380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62977</th>\n",
       "      <td>15.53</td>\n",
       "      <td>464</td>\n",
       "      <td>2</td>\n",
       "      <td>904</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>2</td>\n",
       "      <td>16372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62978</th>\n",
       "      <td>36.07</td>\n",
       "      <td>282</td>\n",
       "      <td>2</td>\n",
       "      <td>760</td>\n",
       "      <td>0.280000</td>\n",
       "      <td>2</td>\n",
       "      <td>22761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62044 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            L  LAST_TO_END  FLIGHT_COUNT  SEG_KM_SUM  avg_discount  label  \\\n",
       "0       90.20            1           210      580717      0.961639      1   \n",
       "1       86.57            7           140      293678      1.252314      1   \n",
       "2       87.17           11           135      283712      1.254676      1   \n",
       "3       68.23           97            23      281336      1.090870      1   \n",
       "4       60.53            5           152      309928      0.970658      1   \n",
       "...       ...          ...           ...         ...           ...    ...   \n",
       "62974  108.30           89             2         368      0.710000      3   \n",
       "62975   65.37          121             2         368      0.670000      3   \n",
       "62976   45.40           39             2        1062      0.225000      4   \n",
       "62977   15.53          464             2         904      0.250000      2   \n",
       "62978   36.07          282             2         760      0.280000      2   \n",
       "\n",
       "          id  \n",
       "0      54993  \n",
       "1      28065  \n",
       "2      55106  \n",
       "3      21189  \n",
       "4      39546  \n",
       "...      ...  \n",
       "62974  11163  \n",
       "62975  30765  \n",
       "62976  10380  \n",
       "62977  16372  \n",
       "62978  22761  \n",
       "\n",
       "[62044 rows x 7 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airline_features['label']=kmeans_model.labels_\n",
    "airline_features['id']=airline['MEMBER_NO']\n",
    "airline_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# airline_features[airline_features['label']==0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 把不同标签的数据放在不同的sheet里面\n",
    "# 找到不同标签的数据 airline_01234\n",
    "airline_0=airline_features[airline_features['label']==0]\n",
    "airline_1=airline_features[airline_features['label']==1]\n",
    "airline_2=airline_features[airline_features['label']==2]\n",
    "airline_3=airline_features[airline_features['label']==3]\n",
    "airline_4=airline_features[airline_features['label']==4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开一个excel文件， airline.xlsx\n",
    "# 把不同客户群体放在不同的sheet里面，命名\n",
    "writer=pd.ExcelWriter('airline.xlsx')\n",
    "airline_0.to_excel(writer,'重点发展客户')\n",
    "airline_1.to_excel(writer,'VIP客户')\n",
    "airline_2.to_excel(writer,'一般客户&流失倾向')\n",
    "airline_3.to_excel(writer,'重要挽留客户')\n",
    "airline_4.to_excel(writer,'一般保持客户')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对于聚类模型的评价\n",
    "# k的选择 CH指数\n",
    "# 类间距/类内距 数字越大越好"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20568.042724708077\n",
      "20192.613813170643\n",
      "19460.24898646162\n",
      "18436.06027505695\n",
      "17870.87401729427\n"
     ]
    }
   ],
   "source": [
    "from sklearn.metrics import calinski_harabasz_score\n",
    "for i in range(5,10):\n",
    "    kmeans=KMeans(n_clusters=i,\n",
    "          random_state=123).fit(airline_features_scaled)\n",
    "    score=calinski_harabasz_score(airline_features_scaled,kmeans.labels_)\n",
    "    print(score)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
